ISRT • SQL Course • Take-home Reference

MySQL Built-in Functions Reference

A compact cheat sheet of MySQL functions you'll meet in the wild. Most won't be covered in class, this is for your future self when you need them.

Compiled by: K.M. Tanvir • Institute of Statistical Research and Training (ISRT), University of Dhaka

How to use: Browse by category in the table of contents. Every entry has the signature, a one-line description, and a short example using the Sakila sample database, so you can paste and adapt.

Already covered in Lecture 5: CONCAT, UPPER, LOWER, LENGTH, ROUND, CEIL, FLOOR, ABS, YEAR, MONTH, DAY. Those are listed below too for completeness.

🔤String functions15 🔢Numeric functions14 📅Date and time functions18 ∅NULL handling and conditionals5 🔄Type conversion2 ∑Aggregate functions (preview)5 ℹ️Session and system info4

🔤String functions

For trimming, splitting, padding, searching, and reshaping text values.

CONCAT(s1, s2, ...)

Joins all arguments end-to-end. NULL anywhere → result is NULL.

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customer;

CONCAT_WS(separator, s1, s2, ...)

Concat With Separator. Joins arguments with a chosen separator and skips NULLs.

SELECT CONCAT_WS(', ', first_name, last_name, email) FROM customer;

UPPER(s) / LOWER(s)

Converts the string to upper / lower case. Useful for case-insensitive matching and display.

SELECT UPPER(first_name), LOWER(email) FROM customer;

LENGTH(s) / CHAR_LENGTH(s)

LENGTH returns bytes, CHAR_LENGTH returns characters. For pure ASCII text the two match; for multi-byte text use CHAR_LENGTH.

SELECT title, CHAR_LENGTH(title) AS chars FROM film;

TRIM(s) / LTRIM(s) / RTRIM(s)

Removes leading and/or trailing whitespace.

SELECT TRIM('   hello   ');  -- 'hello'

SUBSTRING(s, start, length)

Extracts a substring. Position is 1-based.

SELECT SUBSTRING(title, 1, 10) FROM film;  -- first 10 chars

LEFT(s, n) / RIGHT(s, n)

First (or last) n characters of the string.

SELECT LEFT(title, 5), RIGHT(title, 3) FROM film;

REPLACE(s, from, to)

Replaces every occurrence of from with to. Case-sensitive.

SELECT REPLACE(email, '@sakilastaff.com', '@isrt.ac.bd') FROM staff;

INSTR(s, sub) / LOCATE(sub, s)

Returns the position of sub inside s (1-based), or 0 if not found.

SELECT title, INSTR(title, 'MAN') FROM film;

LPAD(s, len, pad) / RPAD(s, len, pad)

Pads the string on the left or right with the pad characters until it reaches the desired length.

SELECT LPAD(customer_id, 5, '0') FROM customer;  -- '00007'

REPEAT(s, n)

Repeats the string n times.

SELECT REPEAT('-', 10);  -- '----------'

REVERSE(s)

Reverses the characters of a string.

SELECT REVERSE('sakila');  -- 'alikas'

FORMAT(num, decimals)

Formats a number as a string with comma thousands separators and the given decimals.

SELECT FORMAT(1234567.891, 2);  -- '1,234,567.89'

SUBSTRING_INDEX(s, delim, count)

Returns the substring before the count-th occurrence of delim. Negative count counts from the end.

SELECT SUBSTRING_INDEX(email, '@', 1) FROM customer;  -- the username

SPACE(n)

Returns a string of n spaces. Mostly used inside CONCAT for alignment.

SELECT CONCAT(first_name, SPACE(2), last_name) FROM customer;

🔢Numeric functions

Rounding, math, randomness, and a few helpers.

ROUND(x, n)

Rounds x to n decimal places (n defaults to 0). Half-values round to even in some MySQL builds.

SELECT ROUND(amount, 1) FROM payment;

CEIL(x) / CEILING(x)

Rounds UP to the next whole number.

SELECT CEIL(amount), amount FROM payment;

FLOOR(x)

Rounds DOWN to the previous whole number.

SELECT FLOOR(amount) FROM payment;

TRUNCATE(x, n)

Chops the decimal at n digits without rounding. TRUNCATE(3.789, 1) → 3.7.

SELECT TRUNCATE(amount, 1) FROM payment;

ABS(x)

Absolute value of x.

SELECT ABS(amount - 5.00) FROM payment;

SIGN(x)

Returns -1, 0, or 1 depending on whether x is negative, zero, or positive.

SELECT SIGN(amount - 5.00) FROM payment;

MOD(a, b) or a % b

Remainder of a / b. Often used for "every nth row" tricks.

SELECT customer_id, MOD(customer_id, 10) FROM customer;

POWER(x, y) or POW(x, y)

x raised to the power of y.

SELECT POWER(2, 10);  -- 1024

SQRT(x)

Square root of x. Returns NULL for negative inputs.

SELECT SQRT(81);  -- 9

EXP(x) / LN(x) / LOG(b, x)

e^x, natural log of x, log of x to base b.

SELECT LN(2.718), LOG(10, 1000);

PI()

Returns π (about 3.14159...).

SELECT PI() * 2;

RAND()

A pseudo-random number in [0, 1). Useful with ORDER BY for random sampling.

SELECT title FROM film ORDER BY RAND() LIMIT 5;

GREATEST(a, b, c, ...) / LEAST(a, b, c, ...)

Returns the largest / smallest of the given values. NOT the same as MAX/MIN aggregates.

SELECT GREATEST(rental_rate, 3.99) FROM film;

DIV / MOD operators

Integer division: a DIV b; remainder: a MOD b or a % b.

SELECT 17 DIV 5, 17 MOD 5;  -- 3, 2

📅Date and time functions

The most useful set of functions for any real-world database. Sakila has plenty of date columns to practice on (rental_date, payment_date, last_update, etc.).

NOW()

Current date and time (a DATETIME). Constant within a single statement.

SELECT NOW();  -- e.g. '2026-05-11 14:30:00'

CURDATE() / CURRENT_DATE

Just today's date (no time).

SELECT CURDATE();  -- '2026-05-11'

CURTIME() / CURRENT_TIME

Just the current time of day.

SELECT CURTIME();  -- '14:30:00'

YEAR(d) / MONTH(d) / DAY(d)

Extracts the year, month, or day-of-month from a DATE or DATETIME.

SELECT YEAR(rental_date), MONTH(rental_date), DAY(rental_date) FROM rental;

HOUR(t) / MINUTE(t) / SECOND(t)

Extracts pieces of a TIME or DATETIME.

SELECT HOUR(rental_date), MINUTE(rental_date) FROM rental;

DAYNAME(d) / MONTHNAME(d)

Returns the English name of the day or month.

SELECT DAYNAME(rental_date) FROM rental;  -- 'Tuesday'

DAYOFWEEK(d) / DAYOFYEAR(d) / WEEK(d)

Numeric variants. DAYOFWEEK uses 1=Sunday by default.

SELECT DAYOFWEEK(rental_date), WEEK(rental_date) FROM rental;

DATE(dt) / TIME(dt)

Extracts just the date portion or just the time portion from a DATETIME.

SELECT DATE(rental_date) FROM rental;  -- drop the time

DATE_ADD(d, INTERVAL n unit) / DATE_SUB

Add or subtract a duration. unit can be DAY, MONTH, YEAR, HOUR, MINUTE, SECOND, etc.

SELECT DATE_ADD(rental_date, INTERVAL 7 DAY) FROM rental;

ADDDATE(d, n) / SUBDATE(d, n)

Shorthand for adding/subtracting n DAYS.

SELECT ADDDATE(rental_date, 7) FROM rental;

DATEDIFF(d1, d2)

Number of days from d2 to d1 (d1 - d2). Negative if d1 is earlier.

SELECT DATEDIFF(return_date, rental_date) AS days_kept FROM rental;

TIMESTAMPDIFF(unit, d1, d2)

Difference between two dates in any unit (YEAR, MONTH, DAY, HOUR, MINUTE, ...). d2 - d1.

SELECT TIMESTAMPDIFF(YEAR, create_date, NOW()) FROM customer;  -- account age in years

DATE_FORMAT(d, fmt)

Format a date using format codes (%Y year, %m month, %d day, %H hour, etc.). The string version of pretty-printing dates.

SELECT DATE_FORMAT(rental_date, '%d-%b-%Y') FROM rental;  -- '14-Jun-2005'

STR_TO_DATE(s, fmt)

Parse a text date back into a DATE using the same format codes. Inverse of DATE_FORMAT.

SELECT STR_TO_DATE('14-06-2005', '%d-%m-%Y');

LAST_DAY(d)

Last day of the month containing d. Handy for month-end reports.

SELECT LAST_DAY('2005-06-14');  -- '2005-06-30'

EXTRACT(unit FROM d)

Standard-SQL equivalent of YEAR/MONTH/DAY etc. but with one unified syntax.

SELECT EXTRACT(YEAR FROM rental_date) FROM rental;

UNIX_TIMESTAMP() / FROM_UNIXTIME(t)

Convert between MySQL date/time and Unix epoch seconds (seconds since 1970).

SELECT UNIX_TIMESTAMP(rental_date) FROM rental;

QUARTER(d)

Returns the quarter (1, 2, 3, or 4) of the date.

SELECT QUARTER(rental_date), COUNT(*) FROM rental GROUP BY 1;

∅NULL handling and conditionals

Tools for turning NULLs into something useful, or for picking between values.

IFNULL(a, b)

If a is NULL, return b; otherwise return a. Handy for default-on-display.

SELECT IFNULL(return_date, 'STILL OUT') FROM rental;

COALESCE(a, b, c, ...)

Returns the first non-NULL argument. The N-way generalization of IFNULL.

SELECT COALESCE(phone, email, 'no contact') FROM customer;

NULLIF(a, b)

Returns NULL if a equals b; otherwise returns a. Useful for turning sentinel values back into NULL.

SELECT NULLIF(phone, '') FROM customer;  -- empty string -> NULL

IF(condition, true_val, false_val)

Inline conditional. Like a ternary operator.

SELECT title, IF(length > 120, 'long', 'short') FROM film;

CASE WHEN ... THEN ... [ELSE ...] END

Multi-branch conditional. The general-purpose if/else of SQL.

SELECT title,
       CASE
         WHEN length < 60  THEN 'short'
         WHEN length < 120 THEN 'medium'
         ELSE 'long'
       END AS length_bucket
FROM film;

🔄Type conversion

Force a value to a specific type, useful when MySQL's automatic coercion gets in your way.

CAST(x AS type)

Standard SQL conversion. Types include CHAR, DATE, DATETIME, DECIMAL, SIGNED, UNSIGNED, TIME.

SELECT CAST('2026-05-11' AS DATE);

CONVERT(x, type)

MySQL's variant with similar effect. CONVERT(x USING utf8) also changes character set.

SELECT CONVERT(amount, CHAR) FROM payment;

∑Aggregate functions (preview)

These work on a group of rows rather than a single row. We'll cover them properly in Lecture 7, but here's a head start.

Note: aggregate functions are commonly used with GROUP BY, which is Lecture 7's main topic. Used alone on the whole table, they collapse all rows into one summary row.

COUNT(*) / COUNT(col)

Number of rows. COUNT(*) counts all rows; COUNT(col) counts rows where col is not NULL.

SELECT COUNT(*) FROM film;

SUM(col)

Total of a numeric column.

SELECT SUM(amount) FROM payment;  -- total revenue

AVG(col)

Mean of a numeric column. Ignores NULLs.

SELECT AVG(rental_rate) FROM film;

MIN(col) / MAX(col)

Smallest / largest value. Works on numbers, strings, and dates.

SELECT MIN(length), MAX(length) FROM film;

GROUP_CONCAT(col SEPARATOR ', ')

Joins all values in the group into a single comma-separated string. Useful for compact listings.

SELECT rating, GROUP_CONCAT(title SEPARATOR ', ') FROM film GROUP BY rating;

ℹ️Session and system info

Little helpers that come up when debugging or writing portable queries.

DATABASE()

Name of the currently selected database (the one you USE'd).

SELECT DATABASE();

USER() / CURRENT_USER()

Username of the current connection.

SELECT USER();

VERSION()

The MySQL server version string. Useful in bug reports.

SELECT VERSION();

LAST_INSERT_ID()

The AUTO_INCREMENT value generated by your most recent INSERT in this session. Common after inserting a parent row and needing the new id for the child row.

INSERT INTO author (name) VALUES ('New Author');
SELECT LAST_INSERT_ID();

← Back to course home

Contents